package com.library.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import com.library.bean.Book;
import com.library.dao.BookDao;
import com.library.util.DBUtil;

public class BookDaoImpl implements BookDao {
	
	JdbcTemplate jt = new JdbcTemplate(DBUtil.getDataSource());
	@Override
	public List<Book> selectAllBooks() {
		String sql = "SELECT * FROM book";
		return jt.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
	}
	@Override
	public List<Book> selectBooks(String keyword) {
		String sql = "SELECT * FROM Book WHERE book_name LIKE ? OR book_writer like  ?";	
		return jt.query(sql, new BeanPropertyRowMapper<Book>(Book.class),"%"+keyword+"%","%"+keyword+"%");
	}
	@Override
	public int deleteBookById(int bookId) {
		String sql = "DELETE FROM book WHERE book_id = ?";
		return jt.update(sql,bookId);
	}
	@Override
	public int insertBook(Book book) {
		String sql = "INSERT INTO book(book_name,book_writer,book_num) VALUES (?,?,?)";
		return jt.update(sql,book.getBookName(),book.getBookWriter(),book.getBookNum());
	}
	@Override
	public Book selectBookById(int bookId) {
		String sql = "SELECT * FROM book WHERE book_id = ?";
		List<Book> list = jt.query(sql,new BeanPropertyRowMapper(Book.class),bookId);
		if(list.size() == 0) {
			return null;
		}else {
			return list.get(0);
		}
	}
	@Override
	public int updateBook(Book book) {
		String sql = "UPDATE book SET book_name = ? , book_writer = ?, book_num = ? WHERE book_id = ?";
		return jt.update(sql,book.getBookName(),book.getBookWriter(),book.getBookNum(),book.getBookId());
	}
	@Override
	public List<Book> selectAllBooks(int pageNum, int pageSize) {
		String sql = "SELECT * FROM book LIMIT ?,?";
		return jt.query(sql, new BeanPropertyRowMapper<Book>(Book.class),(pageNum-1)*pageSize,pageSize);
	}
	@Override
	public int getAllBooksNum() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT COUNT(1) as num FROM book";
		int num = 0;
		try {
			con = DBUtil.getConnection();
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				num = rs.getInt("num");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.closeAll(rs, ps, con);
		}
		return num;
	
	}


}
